"use strict";
/**
 *
 * @author wangchenzhao
 * @date 2018/11/20
 */
const Service = require('egg').Service;

class template extends Service {

  constructor(param) {
    super(param);
    this.mysql = this.app.database.exam_mysql;
  }
  async getAllDataBses() {
    const { mysql } = this;
    //let data = await  mysql.query(sql,[]);
    const sql = `show databases`;
    const result = await mysql.query(sql);
    console.log(result)
    return result;
  }
  async getTablesByDataBase(db_name) {
    const { mysql } = this;
    //let data = await  mysql.query(sql,[]);
    const sql = `select table_schema, table_name,table_comment from information_schema.tables where table_schema='${db_name}' and table_type='base table'`;

    const result = await mysql.query(sql);
    return result;
  }
  async getDetailByTabAndDb(param) {
    const { mysql } = this;
    //let data = await  mysql.query(sql,[]);
    let sql = `SELECT
                        column_name,
                        column_type,
                        data_type,
                        is_nullable,
                        column_comment
                        FROM
                        INFORMATION_SCHEMA.COLUMNS c
                        where
                        c.table_schema ='${param.db_name}'
                        and c.table_name = '${param.tab_name}'`;

    const result = await mysql.query(sql);
    sql = `show create table ${param.db_name}.${param.tab_name}`;
    const createTableDDLRet = await mysql.query(sql);

    return { table_columns: result, ddl: createTableDDLRet[0]['Create Table'] };
  }
  async getDetailByTabNames(param) {
    const { mysql } = this;
    let tableArr = []
    for (const tab_name of param.tab_names) {
      const sql = `SELECT
                            column_name,
                            column_type,
                            data_type,
                            is_nullable,
                            column_comment
                            FROM
                            INFORMATION_SCHEMA.COLUMNS c
                            where
                            c.table_schema ='${param.db_name}'
                            and c.table_name = '${tab_name}'`;

      const result = await mysql.query(sql);
      tableArr.push({ tab_name: tab_name, data: result })
    }
    return tableArr;
  }
  async getSqlByTabNames(param) {
    const { mysql } = this;
    let tableSqls = []
    for (const tab_name of param.tab_names) {

      const sql = `show create table ${param.db_name}.${tab_name}`;
      const createTableDDLRet = await mysql.query(sql);


      tableSqls.push({ ddl: createTableDDLRet[0]['Create Table'] })
    }
    return tableSqls;
  }
  async getTablesData(param) {
    const { mysql } = this;
    let tableArr = []
    for (const tab_name of param.tab_names) {
      let sql = `SELECT
                            column_name,
                            column_comment
                            FROM
                            INFORMATION_SCHEMA.COLUMNS c
                            where
                            c.table_schema ='${param.db_name}'
                            and c.table_name = '${tab_name}'`;

      const columns = await mysql.query(sql);
      const pageing = await this.paging(param.page_num);
      sql = `use ${param.db_name};
                         select * from ${'`' + tab_name + '`'} limit ${pageing.start}, ${pageing.page_size};`;

      const result = await mysql.query(sql);

      tableArr.push({ tab_name: tab_name, columns: columns, data: result[1] })
    }
    console.log(tableArr)
    return tableArr;
  }
  async getOneTableData(param) {
    const { mysql } = this;
    if (!param.db_name) return {
      db_name: '',
      db_list: [],
      tab_name: '',
      tab_list: [],
      table_comment: '',
      columns: [],
      count: 0,
      page_count: 0,
      data: []
    };
    let sql = `
            
        SELECT
            table_schema,
            table_name,
            table_comment 
        FROM
            information_schema.TABLES 
        WHERE
            
            table_type = 'base table' 
            AND  table_schema = '${param.db_name}'
        `
    const tables = await mysql.query(sql);
    if (!param.tab_name) return {
      db_name: param.db_name,
      db_list: [],
      tab_name: '',
      tab_list: tables,
      table_comment: '',
      columns: [],
      count: 0,
      page_count: 0,
      data: []
    };
    sql = `SELECT
                            column_name,
                            column_comment
                            FROM
                            INFORMATION_SCHEMA.COLUMNS c
                            where
                            c.table_schema ='${param.db_name}'
                            and c.table_name = '${param.tab_name}'`;

    const columns = await mysql.query(sql);
    let columns_bootstrip = [{
      checkbox: "true",
      field: 'check',
      align: 'center',
      valign: 'middle'
    }, {
      title: "编号",
      field: 'ID',
      align: 'left',
      valign: 'middle',
      width: '1%'
    },];
    if (columns.length > 0) {
      for (const iterator of columns) {

        columns_bootstrip.push({
          title: iterator.column_comment === '' || !iterator.column_comment ? ` <span style=color:red>${iterator.column_name}</span>` : `<span style=color:red>(${iterator.column_name})</span> ${iterator.column_comment}`,
          field: iterator.column_name,
          align: 'left',
          valign: 'middle',
          class: 'colStyle',
          // formatter:
          // function paramsMatter(value, row, index) {
          //     var values = row.params;//获取当前字段的值
          //     //替换空格，因为字符串拼接的时候如果遇到空格，会自动将后面的部分截掉，所有这里用html的转义符
          //     // 代替
          //     values = values.replace(/\s+/g,' ')
          //     return "<span title="+values+">"+row.params+"</span>"
          // }

        })
      }
    }
    console.log(columns_bootstrip)
    // const pageing = await this.paging(param.page_num);
    sql = `use ${param.db_name};
                  select count(*) count from ${'`' + param.tab_name + '`'}`;
    const count_ret = await mysql.query(sql);
    const count = count_ret[1][0]['count'];
    sql = ` use ${param.db_name};
                    select * from ${'`' + param.tab_name + '`'}` // limit ${pageing.start}, ${pageing.page_size};`

    const result = await mysql.query(sql);
    sql = `
            
                    SELECT
                        table_schema,
                        table_name,
                        table_comment 
                    FROM
                        information_schema.TABLES 
                    WHERE
                        
                        table_type = 'base table' 
                        AND  table_schema = '${param.db_name}'
                    `
    // const tables = await mysql.query(sql);
    sql += ` AND table_name = '${param.tab_name}'`
    const table = await mysql.query(sql);

    return {
      db_name: param.db_name,
      tab_list: tables,
      tab_name: param.tab_name,
      table_comment: table[0].table_comment,
      columns: columns_bootstrip,//JSON.stringify(columns_bootstrip),
      columns_key: columns,
      count: count,
      // page_count: count === 0 ? 0 : Math.ceil(count / pageing.page_size),
      data: result[1]
    };
  }

  async getTablesData(param) {
    const { mysql } = this;
    let tableArr = []
    for (const tab_name of param.tab_names) {
      let sql = `SELECT
                            column_name,
                            column_comment
                            FROM
                            INFORMATION_SCHEMA.COLUMNS c
                            where
                            c.table_schema ='${param.db_name}'
                            and c.table_name = '${tab_name}'`;

      const columns = await mysql.query(sql);
      const pageing = await this.paging(param.page_num);
      sql = `use ${param.db_name};
                         select * from ${'`' + tab_name + '`'} limit ${pageing.start}, ${pageing.page_size};`;

      const result = await mysql.query(sql);

      tableArr.push({ tab_name: tab_name, columns: columns, data: result[1] })
    }
    console.log(tableArr)
    return tableArr;
  }
  async getOneTableData2(param) {
    const { mysql } = this;
    if (!param.db_name) return {
      db_name: '',
      db_list: [],
      tab_name: '',
      tab_list: [],
      table_comment: '',
      columns: [],
      count: 0,
      page_count: 0,
      data: []
    };
    let sql = `
            
        SELECT
            table_schema,
            table_name,
            table_comment 
        FROM
            information_schema.TABLES 
        WHERE
            
            table_type = 'base table' 
            AND  table_schema = '${param.db_name}'
        `
    const tables = await mysql.query(sql);
    if (!param.tab_name) return {
      db_name: param.db_name,
      db_list: [],
      tab_name: '',
      tab_list: tables,
      table_comment: '',
      columns: [],
      count: 0,
      page_count: 0,
      data: []
    };
    sql = `SELECT
                            column_name,
                            column_comment
                            FROM
                            INFORMATION_SCHEMA.COLUMNS c
                            where
                            c.table_schema ='${param.db_name}'
                            and c.table_name = '${param.tab_name}'`;

    const columns = await mysql.query(sql);
    let columns_bootstrip = [{
      checkbox: "true",
      field: 'check',
      align: 'center',
      valign: 'middle'
    }, {
      title: "编号",
      field: 'ID',
      align: 'left',
      valign: 'middle',
      width: '1%'
    },];
    if (columns.length > 0) {
      for (const iterator of columns) {

        columns_bootstrip.push({
          title: iterator.column_comment === '' || !iterator.column_comment ? ` <span style=color:red>${iterator.column_name}</span>` : `<span style=color:red>(${iterator.column_name})</span> ${iterator.column_comment}`,
          field: iterator.column_name,
          align: 'left',
          valign: 'middle',
          class: 'colStyle',
          // formatter:
          // function paramsMatter(value, row, index) {
          //     var values = row.params;//获取当前字段的值
          //     //替换空格，因为字符串拼接的时候如果遇到空格，会自动将后面的部分截掉，所有这里用html的转义符
          //     // 代替
          //     values = values.replace(/\s+/g,' ')
          //     return "<span title="+values+">"+row.params+"</span>"
          // }

        })
      }
    }
    console.log(columns_bootstrip)
    // const pageing = await this.paging(param.page_num);
    sql = `use ${param.db_name};
                  select count(*) count from ${'`' + param.tab_name + '`'}`;
    const count_ret = await mysql.query(sql);
    const count = count_ret[1][0]['count'];
    sql = ` use ${param.db_name};
                    select * from ${'`' + param.tab_name + '`'}` // limit ${pageing.start}, ${pageing.page_size};`

    const result = await mysql.query(sql);
    sql = `
            
                    SELECT
                        table_schema,
                        table_name,
                        table_comment 
                    FROM
                        information_schema.TABLES 
                    WHERE
                        
                        table_type = 'base table' 
                        AND  table_schema = '${param.db_name}'
                    `
    // const tables = await mysql.query(sql);
    sql += ` AND table_name = '${param.tab_name}'`
    const table = await mysql.query(sql);

    return {
      db_name: param.db_name,
      tab_list: tables,
      tab_name: param.tab_name,
      table_comment: table[0].table_comment,
      columns: columns_bootstrip,//JSON.stringify(columns_bootstrip),
      columns_key: columns,
      count: count,
      // page_count: count === 0 ? 0 : Math.ceil(count / pageing.page_size),
      data: result[1]
    };
  }
  async paging(page_num) {
    const { mysql } = this;
    // 暂定每页20条
    const page_size = 20;
    const start = (page_num - 1) * 20;
    return { page_size, start };

  }
}

module.exports = template;